/* 
 * Copyright 2014-2021 UME Framework, GNU General Public License 
 */
package com.u2framework.lib.tool.exp.table.exporter;

import java.sql.Date;
import java.sql.Time;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import com.alibaba.fastjson.JSON;
import com.u2framework.lib.core.util.SqlUtil;
import com.u2framework.lib.core.util.StringUtil;
import com.u2framework.lib.tool.exp.table.bean.ColumnDescBean;
import com.u2framework.lib.tool.exp.table.bean.TableStructExportBean;
import com.u2framework.lib.tool.exp.table.bean.TableStructExportParam;

/**
 * 1.根据Excel输入的Table列表信息及数据库连接信息，查询数据字典获取表定义的结构并导出数据库中的既存数据。 <br>
 * 2.根据上述模版结构编辑数据，将编辑后的数据生成可更新至数据库的SQL。<br>
 * 
 * @author SultansOfSwing & MaYue
 */
public class TableDataExporter extends TableStructExporter {

    /**
     * 构造器。
     */
    public TableDataExporter(String databaseType) {
        super(databaseType);
    }
    /**
     * 导出多个表的结构信息。
     *
     * @param inputDto
     * @return
     * @throws Throwable
     */
    @Override
    public TableStructExportBean expTableInfo(TableStructExportParam inputDto, DataSource ds) throws Throwable {
        TableStructExportBean tableStructExportBean = super.expTableInfo(inputDto, ds);
        List<ColumnDescBean> columnDescList = tableStructExportBean.getColumnDescList();

        String schema = inputDto.getSchema();
        String table = inputDto.getTable();
        String whereCondition = inputDto.getWhereCondition();
        String orderByCondition = inputDto.getOrderByCondition();
        Integer pageNum = inputDto.getPageNum();
        if (pageNum == null) {
            pageNum = 1;
        }
        Integer pageSize = inputDto.getPageSize();
        if (pageSize == null) {
            pageSize = 1000;
        }
        
        logger.info("Start extract table data of " + table + (StringUtil.isEmpty(whereCondition) ? "" : ",condition by " + whereCondition));
        String tableDataCountStr = createTableDataCountStr(columnDescList, schema, table, whereCondition);
        int total = createJdbcDao(ds).count(tableDataCountStr, null);
        logger.info("Count size:" + total);
        if (total > pageSize) {
            throw new RuntimeException("表<" + schema + "." + table + ">的数据抽取量超过最大件数限制。当前数据总量:" + total + ", 当前最大抽取数限制:" + pageSize);
        }

        String tableDataQueryStr = createTableDataQueryStr(columnDescList, schema, table, whereCondition, orderByCondition, pageNum, pageSize);
        List<Map<String, Object>> tableData = createJdbcDao(ds).queryForMapList(tableDataQueryStr, null);
        logger.info("Extracted " + (tableData != null ? tableData.size() : "0") + " record of " + table);

        table = table.length() > 30 ? table.substring(0, 29) : table;
        tableStructExportBean.setRowList(tableData);

        return tableStructExportBean;
    }

    /**
     * 创建根据表抽取的数据来创建Insert SQL。
     *
     * @param tableName
     * @param rowList
     * @return
     * @throws Exception
     */
    public List<String> buildTableInsertSQL(String tableName, List<List<Object>> rowList) throws Exception {
        List<String> sqlList = new ArrayList<String>();
        StringBuilder sql = new StringBuilder();
        sql.append("DELETE FROM " + tableName + " ;");
        sqlList.add(sql.toString());
        List<Object> columnList = rowList.get(1);
        List<Object> typeList = rowList.get(2);
        for (int rowIndex = 3; rowIndex < rowList.size(); rowIndex++) {
            List<Object> datas = rowList.get(rowIndex);
            sql = new StringBuilder();
            sql.append("INSERT INTO " + tableName + "(");
            for (int k = 0; k < columnList.size(); k++) {
                if (k > 0) {
                    sql.append(",");
                }
                sql.append(columnList.get(k));
            }
            sql.append(") VALUES (");
            for (int k = 0; k < datas.size(); k++) {
                if (k > 0) {
                    sql.append(",");
                }
                sql.append(convertValueForInsertSQL(typeList.get(k).toString(), datas.get(k) == null ? "" : datas.get(k).toString()));
            }
            sql.append(") ;");
            sqlList.add(sql.toString());
        }
        logger.info("Generate SQL for " + tableName);
        return sqlList;
    }

    /**
     * 创建根据表抽取的数据来创建JSON数据。
     *
     * @param tableName
     * @param rowList
     * @param useCamelCase
     * @return
     * @throws Exception
     */
    public List<String> buildTableJsonData(String tableName, List<List<Object>> rowList, boolean useCamelCase) throws Exception {
        List<String> jsonList = new ArrayList<String>();
        List<Object> columnList = rowList.get(1);
        List<Object> typeList = rowList.get(2);
        for (int rowIndex = 3; rowIndex < rowList.size(); rowIndex++) {
            List<Object> datas = rowList.get(rowIndex);
            LinkedHashMap<String, Object> bean = new LinkedHashMap<String, Object>();
            for (int k = 0; k < datas.size(); k++) {
                String colName = columnList.get(k).toString();
                String dataStr = datas.get(k).toString();
                Object dataObj = null;
                String typeStr = typeList.get(k).toString().toUpperCase();
                typeStr = typeStr.contains("(") ? typeStr.substring(0, typeStr.indexOf("(")) : typeStr;

                if (dataStr == null || dataStr.trim().equals("")) {
                    dataObj = null;
                } else if (typeStr.equals("CHARACTER") || typeStr.equals("CHAR") || typeStr.equals("VARCHAR") || typeStr.equals("VARCHAR2") || typeStr.equals("LONGVARCHAR") || typeStr.equals("TEXT") || typeStr.equals("TINYTEXT")) {
                    dataObj = dataStr.contains("'") ? dataStr.replace("'", "''") : dataStr;
                } else if (typeStr.equals("TIMESTAMP") || typeStr.equals("DATETIME")) {
                    dataObj = Timestamp.valueOf(dataStr);
                } else if (typeStr.equals("TIME")) {
                    dataObj = Time.valueOf(dataStr);
                } else if (typeStr.equals("DATE")) {
                    dataObj = Date.valueOf(dataStr);
                } else if (typeStr.equals("TINYINT") || typeStr.equals("SMALLINT") || typeStr.equals("MEDIUMINT") || typeStr.equals("INTEGER") || typeStr.equals("INT") || typeStr.equals("BIGINT")) {
                    dataObj = Long.valueOf(dataStr);
                } else if (typeStr.equals("FLOAT") || typeStr.equals("DOUBLE") || typeStr.equals("DECIMAL")) {
                    dataObj = Double.valueOf(dataStr);
                } else if (typeStr.equals("BOOLEAN")) {
                    dataObj = Boolean.valueOf(dataStr);
                } else {
                    dataObj = dataStr;
                }

                if (useCamelCase) {
                    colName = toCamelCase(colName);
                }
                bean.put(colName, dataObj);
            }

            String json = JSON.toJSONString(bean);
            jsonList.add(json);
        }
        logger.info("Generate json data for " + tableName);
        return jsonList;
    }

    /**
     * convertValueForInsertSQL
     * 
     * @param type
     * @param value
     * @return
     */
    protected String convertValueForInsertSQL(String type, String value) {
        String result = null;
        type = type.toUpperCase();
        type = type.contains("(") ? type.substring(0, type.indexOf("(")) : type;
        if (type.equals("CHARACTER") || type.equals("CHAR") || type.equals("CLOB") || type.equals("VARCHAR") || type.equals("VARCHAR2") || type.equals("NVARCHAR") || type.equals("NVARCHAR2") || type.equals("LONGVARCHAR")) {
            if (StringUtil.isEmpty(value)) {
                result = null;
            } else if (value.contains("'")) {
                value = value.replace("'", "''");
                result = "'" + value + "'";
            } else {
                result = "'" + value + "'";
            }
        } else if (!StringUtil.isEmpty(value) && (type.equalsIgnoreCase("TIMESTAMP") || type.equalsIgnoreCase("DATETIME"))) {
            result = SqlUtil.getToTimestampFuncStr(this.getDatabaseType(), value);
        } else if (!StringUtil.isEmpty(value) && type.equalsIgnoreCase("DATE")) {
            result = SqlUtil.getToDateFuncStr(this.getDatabaseType(), value);
        } else if (!StringUtil.isEmpty(value) && type.equals("TIME")) {
            result = SqlUtil.getToTimeFuncStr(this.getDatabaseType(), value);
        } else {
            result = value;
        }
        return StringUtil.isEmpty(result) ? "null" : result;
    }

    /**
     * 创建表数据查询SQL语句
     * 
     * @param desc
     * @param schemaName
     * @param tableName
     * @param whereCondition
     * @return
     */
    protected String createTableDataQueryStr(List<ColumnDescBean> desc, String schemaName, String tableName, String whereCondition, String orderByCondition, int pageNo, int pageSize) {
        StringBuilder builder = new StringBuilder();
        builder.append("SELECT ");
        for (int i = 0; i < desc.size(); i++) {
            if (i > 0) {
                builder.append(",");
            }
            builder.append(desc.get(i).getPropertyId());
        }
        builder.append(" FROM ");
        if ("MYSQL".equalsIgnoreCase(this.getDatabaseType())) {
            builder.append('`');
        }
        builder.append(schemaName);
        if ("MYSQL".equalsIgnoreCase(this.getDatabaseType())) {
            builder.append('`');
        }
        builder.append(".");
        if ("MYSQL".equalsIgnoreCase(this.getDatabaseType())) {
            builder.append('`');
        }
        builder.append(tableName);
        if ("MYSQL".equalsIgnoreCase(this.getDatabaseType())) {
            builder.append('`');
        }
        if (whereCondition != null && !whereCondition.trim().equals("")) {
            builder.append(" WHERE ");
            builder.append(whereCondition);
        }
        if (orderByCondition != null && !orderByCondition.trim().equals("")) {
            builder.append(" ORDER BY ");
            builder.append(orderByCondition);
        }

        return buildSelectPagination(this.getDatabaseType(), builder.toString(), pageNo, pageSize);
    }
    
    /**
     * buildSelectPagination 
     *
     * @param databaseType
     * @param sql
     * @param pageNo
     * @param pageSize
     * @return
     */
    private String buildSelectPagination(String databaseType, String sql, int pageNo, int pageSize) {
        if (databaseType.equalsIgnoreCase("ORACLE")) {
            // ORACLE
            StringBuilder sqlBuilder = new StringBuilder("SELECT * FROM (SELECT ROWNUM AS ROWNUM_, T1.* FROM (");
            sqlBuilder.append(sql);
            sqlBuilder.append(") T1 WHERE ROWNUM < ");
            sqlBuilder.append(pageNo * pageSize + 1);
            sqlBuilder.append(") T2 WHERE ROWNUM_ > ");
            sqlBuilder.append((pageNo - 1) * pageSize);
            return sqlBuilder.toString();
        } else {
            // MySQL
            StringBuilder sqlBuilder = new StringBuilder(sql);
            sqlBuilder.append(" LIMIT ");
            sqlBuilder.append((pageNo - 1) * pageSize);
            sqlBuilder.append(",");
            sqlBuilder.append(pageSize);
            return sqlBuilder.toString();
        }
    }

    /**
     * 创建表数据CountSQL语句
     * 
     * @param desc
     * @param schemaName
     * @param tableName
     * @param whereCondition
     * @return
     */
    protected String createTableDataCountStr(List<ColumnDescBean> desc, String schemaName, String tableName, String whereCondition) {
        StringBuilder builder = new StringBuilder();
        builder.append("SELECT COUNT(*) ");
        builder.append(" FROM ");
        if ("MYSQL".equalsIgnoreCase(this.getDatabaseType())) {
            builder.append('`');
        }
        builder.append(schemaName);
        if ("MYSQL".equalsIgnoreCase(this.getDatabaseType())) {
            builder.append('`');
        }
        builder.append(".");
        if ("MYSQL".equalsIgnoreCase(this.getDatabaseType())) {
            builder.append('`');
        }
        builder.append(tableName);
        if ("MYSQL".equalsIgnoreCase(this.getDatabaseType())) {
            builder.append('`');
        }
        if (whereCondition != null && !whereCondition.trim().equals("")) {
            builder.append(" WHERE ");
            builder.append(whereCondition);
        }
        return builder.toString();
    }

}
